﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.SqlClient;
using HZ.Data;
namespace HZ.DrugTrade.DAL
{
    public class DAL_DistributionRelation
    {

        /// <summary>
        /// 批量增加配送关系
        /// </summary>
        /// <param name="distributionrelationList">配送关系集合</param>
        /// <param name="flag">0直接添加；1从采购目录及企业角度增加医疗机构；2从采购目录及医疗机构角度增加企业；3从企业及医疗机构角度增加采购目录</param>
        public static int AddDistribution_Batch(IList<HZ.DrugTrade.Model.DistributionRelation> distributionrelationList, int flag, HZ.DrugTrade.Model.DistributionRelation distributionrelation)
        {
            int Success = 0;
            if (flag == 0 && distributionrelation == null)
            {
                return 0;
            }
            IDataParameter[] paras = null;
            string strSplit = ",";
            switch (flag)
            {
                case 0://0直接添加
                    paras = new IDataParameter[] {
                new SqlParameter("@in_ProcureCatalogIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionrelationList,"ProcureCatalogID"  ,strSplit)),
                new SqlParameter("@in_CompanyID_PSs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionrelationList,"CompanyID_PS"  ,strSplit)),
                new SqlParameter("@in_HospitalIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionrelationList,"HospitalID"  ,strSplit)),            
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
                    paras[paras.Length - 1].Direction = ParameterDirection.Output;
                    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_DistributionRelation_Batch_Add", paras);
                    break;

                case 1://1从采购目录及企业角度增加医疗机构
                    paras = new IDataParameter[] {
                new SqlParameter("@in_ProcureCatalogID", distributionrelation.ProcureCatalogID),
                new SqlParameter("@in_CompanyID_PS", distributionrelation.CompanyID_PS),        
                new SqlParameter("@in_HospitalIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionrelationList,"HospitalID"  ,strSplit)),  
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
                    paras[paras.Length - 1].Direction = ParameterDirection.Output;
                    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_DistributionRelationByProcureCatalogCompany_Batch_Add", paras);
                    break;

                case 2://2从采购目录及医疗机构角度增加企业
                    paras = new IDataParameter[] {
               new SqlParameter("@in_ProcureCatalogID", distributionrelation.ProcureCatalogID),
                new SqlParameter("@in_CompanyID_PSs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionrelationList,"CompanyID_PS"  ,strSplit)),
                new SqlParameter("@in_HospitalID", distributionrelation.HospitalID),            
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
                    paras[paras.Length - 1].Direction = ParameterDirection.Output;
                    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_DistributionRelationByProcureCatalogHospital_Batch_Add", paras);
                    break;

                case 3://3从企业及医疗机构角度增加采购目录
                    paras = new IDataParameter[] { 
                new SqlParameter("@in_ProcureCatalogIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionrelationList,"ProcureCatalogID"  ,strSplit)),
                new SqlParameter("@in_CompanyID_PS", distributionrelation.CompanyID_PS),        
                new SqlParameter("@in_HospitalID", distributionrelation.HospitalID),            
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
                    paras[paras.Length - 1].Direction = ParameterDirection.Output;
                    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_DistributionRelationByCompanyHospital_Batch_Add", paras);
                    break;
                default:
                    break;
            }

            Success = (int)paras[paras.Length - 1].Value;

            return Success;

        }


        /// <summary>
        /// 删除配送关系
        /// </summary>
        /// <param name="distributionrelation">配送关系【ProcureCatalogID，CompanyID_PS，HospitalID必填】</param>
        public static int DelDistribution(HZ.DrugTrade.Model.DistributionRelation distributionrelation)
        {
            IDataParameter[] paras = new IDataParameter[] { 
                new SqlParameter("@in_ProcureCatalogID", distributionrelation.ProcureCatalogID),
                new SqlParameter("@in_CompanyID_PS", distributionrelation.CompanyID_PS),        
                new SqlParameter("@in_HospitalID", distributionrelation.HospitalID),            
                new SqlParameter("@out_Success",SqlDbType.Int)
            };

            paras[paras.Length - 1].Direction = ParameterDirection.Output;
            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_DistributionRelation_Del", paras);
            return (int)paras[paras.Length - 1].Value;
        }


        /// <summary>
        /// 条件获取配送关系
        /// </summary>
        /// <param name="distribution">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        public static IList<HZ.DrugTrade.Model.View.DistributionRelation> GetDistributionByWhere(HZ.DrugTrade.Model.Query.DistributionRelation query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.DistributionRelation>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_DistributionRelation WHERE 1=1 {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.DistributionRelation>(dt);
        }

        /// <summary>
        /// 条件获取配送情况
        /// </summary>
        /// <param name="distribution">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        public static IList<HZ.DrugTrade.Model.View.Distribution> GetDistributionByWhere(HZ.DrugTrade.Model.Query.Distribution query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Distribution>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT  *
                                                                                                FROM View_OrderDetailDistribution 
                                                                                                WHERE OrderDetailState=2 AND DistributeCount>0  {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Distribution>(dt);
        }

        /// <summary>
        /// 条件获取未配送信息
        /// </summary>
        /// <param name="distribution">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        public static IList<HZ.DrugTrade.Model.View.Distribution> GetNoDistributionByWhere(HZ.DrugTrade.Model.Query.Distribution query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Distribution>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT * 
                                                                                                FROM View_OrderDetailDistribution 
                                                                                                WHERE OrderDetailState=2 AND PurchaseCount >DistributeCount {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Distribution>(dt);
        }

        /// <summary>
        /// 条件获取采购单明细配送单情况信息（DistributeState：0.待验收；1.已验收）
        /// </summary>
        /// <param name="distribution">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        public static IList<HZ.DrugTrade.Model.View.Distribution> GetNoWarehouseByWhere(HZ.DrugTrade.Model.Query.Distribution query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Distribution>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT * FROM View_Distribution WHERE  OrderDetailState=2 
AND Dis_DistributeCount IS NOT NULL {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Distribution>(dt);
        }

        /// <summary>
        /// 条件获取已入库（验收）信息
        /// </summary>
        /// <param name="distribution">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        public static IList<HZ.DrugTrade.Model.View.Distribution> GetWarehouseByWhere(HZ.DrugTrade.Model.Query.Distribution query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Distribution>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT * FROM View_Distribution WHERE  OrderDetailState=2 
AND DistributeState=1 {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Distribution>(dt);
        }


        /// <summary>
        /// 条件获取已入库（验收）信息
        /// </summary>
        /// <param name="distribution">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        public static IList<HZ.DrugTrade.Model.View.Distribution> GetPurchaseOrderDetail_DistributeNoInvoiceByWhere(HZ.DrugTrade.Model.Query.Distribution query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.Distribution>(query, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT * FROM View_Distribution WHERE  OrderDetailState=2 
AND IsInvoice=0 AND purchasecount > 0 {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Distribution>(dt);
        }
        /// <summary>
        /// 新增非中标产品配送企业
        /// </summary>
        /// <param name="distributionrelation"></param>
        /// <returns></returns>
        public static int AddDistributionUnBid(HZ.DrugTrade.Model.DistributionRelation distributionrelation)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                int count = int.Parse(h.ExecuteScalar(string.Format(@"SELECT COUNT(*) FROM TB_DistributionRelation
WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}' ", distributionrelation.ProcureCatalogID, distributionrelation.HospitalID)).ToString());

                if (count == 0)
                {
                    distributionrelation.IsUsing = true;
                    // h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetInsertSql(distributionrelation));
                    HZ.Data.DAL_Helper.OperateTableSQL<HZ.DrugTrade.Model.DistributionRelation>(distributionrelation, HZ.Data.TableOperator.Insert, h);
                }
                else
                {
                    h.ExecuteNonQuery(string.Format(@"UPDATE TB_DistributionRelation SET IsUsing = 1,Price='{2}',CompanyID_PS='{3}' WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}'
", distributionrelation.ProcureCatalogID, distributionrelation.HospitalID, distributionrelation.Price, distributionrelation.CompanyID_PS));

                }
                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_DistributionRelation", ee.Message, ee);
                return 0;
            }

        }

        /// <summary>
        /// 新增配送关系
        /// </summary>
        /// <param name="distributionrelation"></param>
        /// <returns></returns>
        public static int AddDistribution(HZ.DrugTrade.Model.DistributionRelation distributionrelation)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                int count = int.Parse(h.ExecuteScalar(string.Format(@"SELECT COUNT(*) FROM TB_DistributionRelation
WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}' AND CompanyID_PS = '{2}'", distributionrelation.ProcureCatalogID, distributionrelation.HospitalID, distributionrelation.CompanyID_PS)).ToString());

                if (count == 0)
                {
                    h.ExecuteNonQuery(string.Format(@"UPDATE TB_DistributionRelation SET IsUsing = 0
WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}'", distributionrelation.ProcureCatalogID, distributionrelation.HospitalID));
                    distributionrelation.IsUsing = true;
                   // h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetInsertSql(distributionrelation));
                    HZ.Data.DAL_Helper.OperateTableSQL<HZ.DrugTrade.Model.DistributionRelation>(distributionrelation,HZ.Data.TableOperator.Insert,h);
                }
                else
                {
                    //                    distributionrelation.IsUsing = true;
                    //                    h.ExecuteNonQuery(string.Format(@"UPDATE TB_DistributionRelation SET IsUsing = 0 WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}';
                    //UPDATE TB_DistributionRelation SET IsUsing = 1 WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}' AND CompanyID_PS = '{2}'
                    //", distributionrelation.ProcureCatalogID, distributionrelation.HospitalID, distributionrelation.CompanyID_PS));


                    distributionrelation.IsUsing = true;
                    h.ExecuteNonQuery(string.Format(@"UPDATE TB_DistributionRelation SET IsUsing = 0 WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}';
UPDATE TB_DistributionRelation SET IsUsing = 1,Price={2} WHERE ProcureCatalogID = '{0}' AND HospitalID = '{1}' AND CompanyID_PS = '{3}'
", distributionrelation.ProcureCatalogID, distributionrelation.HospitalID, distributionrelation.Price, distributionrelation.CompanyID_PS));

                    //Price

                }
                h.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_DistributionRelation", ee.Message, ee);
                return 0;
            }

        }

        public static IList<HZ.DrugTrade.Model.View.DistributionRelation> GetHospitalProcureCatalog_With_DefaultDistribution_Company(HZ.DrugTrade.Model.Query.DistributionRelation distribution, int currentpage, int pagesize, out int totalrecord)
        {

            string sql = string.Format(@"SELECT * FROM View_HospitalProcureCatalog_Company WHERE 1=1 {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(distribution, true));
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.DistributionRelation>(des.GetSpecailPage());
        }
    }
}
